const Database = require("better-sqlite3");

var userdb = new Database(path.join(__dirname, `/databases/_user.db`), {verbose: console.log});
const userTableExist = userdb.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ? COLLATE NOCASE"); // 检查表是否存在
if (!userTableExist.get('user')){
    userdb.exec("CREATE TABLE user( \
        ID  INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL, \
        account_name TEXT  NOT NULL, \
        user_name TEXT  NOT NULL, \
        email TEXT  NOT NULL, \
        password_SHA256 TEXT  NOT NULL \
    )");
}

function calcSHA256(input) {
    const hash = crypto.createHash('sha256');
    hash.update(input);
    return hash.digest('hex');
}

/**
 * 在数据库中增加用户
 * @param {String} account_name 账户名
 * @param {String} user_name 用户真名
 * @param {String} email 邮箱
 * @param {String} password 明文密码
 */
function addUser(account_name, user_name, email, password){
    var password_SHA256 = calcSHA256(password);
    userdb.prepare(`INSERT INTO user (account_name, user_name, email, password_SHA256) VALUES (?, ?, ?, ?)`).run(account_name, user_name, email, password_SHA256);

    var db = new Database(path.join(__dirname, `/databases/${account_name}.db`), {verbose: console.log});
    const tableExist = db.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ? COLLATE NOCASE"); // 检查表是否存在
    if (!tableExist.get('task')){
        db.exec("CREATE TABLE task( \
            ID  INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL, \
            category  TEXT  NOT NULL, \
            summary  TEXT  NOT NULL, \
            quantity  INTEGER, \
            progress  INTEGER, \
            note  TEXT, \
            doneFlag BOOLEAN NOT NULL, \
            targetFlag SMALLINT NOT NULL \
        )");
    }
    if (!tableExist.get("log")){
        db.exec("CREATE TABLE log( \
            ID  INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL, \
            date TEXT NOT NULL, \
            start  TEXT  NOT NULL, \
            taskID  INTEGER  NOT NULL, \
            end  TEXT, \
            quantity  INTEGER, \
            note  TEXT \
        )");
    }
}

/**
 * 获取所有用户
 * @returns 用户列表
 */
function getAllUser(){
    return userdb.prepare('SELECT * FROM user;').all();
}

/**
 * 修改指定任务的数量
 * @param {String} account_name 账户名
 * @param {Number} taskID 任务ID
 * @param {Number} targetQuantity 更改后的数量
 */
function changeTaskSize(account_name, taskID, targetQuantity){
    var db = new Database(path.join(__dirname, `/databases/${account_name}.db`), {verbose: console.log});
    const changeTaskQuantityByID = db.prepare('UPDATE task SET quantity = ? WHERE ID = ?');
    changeTaskQuantityByID.run(targetQuantity, taskID);
}


module.exports = {
    calcSHA256,
    addUser,
    getAllUser,
    changeTaskSize
};